import pandas as pd
import statsmodels.formula.api as smf
from sqlalchemy import create_engine
import pymysql

db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': 'sjk1234',
    'database': 'tushare',
    'port': 3306,
    'charset': 'utf8mb4'
}

engine = create_engine(f'mysql+pymysql://{db_config["user"]}:{db_config["password"]}@{db_config["host"]}:{db_config["port"]}/{db_config["database"]}?charset={db_config["charset"]}')

conn = pymysql.connect(**db_config)
chunk_size = 10000

# 获取华夏银行日线数据
df = pd.read_sql_query("SELECT * FROM date_1 WHERE date_1.trade_date BETWEEN '2023-01-01' AND '2023-12-31' and date_1.ts_code = '600015.SZ'",
                       conn,
                       chunksize=chunk_size)
df1 = pd.concat(df, ignore_index=True)

df1['_d_closes'] = round((df1['closes'] - df1['closes'].shift(1)) / df1['closes'].shift(1), 2)

# 处理缺失数据
df1 = df1.dropna(subset=['_d_closes'])
print(df1)

ex = ['id', 'ts_code', 'trade_date', 'the_date', 'opens', 'high', 'low', 'closes', 'pre_closes', 'changes', 'pct_chg', 'vol', 'amount']
number = df1.select_dtypes(include=['number']).columns.to_list()
number_list = [i for i in number if i not in ex]

formula = '_d_closes ~ ' + ' + '.join(number_list)
res = smf.ols(formula, data=df1).fit()
print(res.summary())